package com.literature.threeGroup.util;

import com.literature.threeGroup.pojo.Purchase;
import com.literature.threeGroup.pojo.ResourceInfo;
import com.literature.threeGroup.pojo.ResourceUpdate;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author ：mmzs
 * @date ：Created in 2020/1/31 18:45
 * @description：封装类
 * @modified By：
 * @version: 1.0$
 */
public class POIUtil {

    public static void export(List<ResourceInfo> userList) throws Exception{
        //指定数据存放的位置
        OutputStream outputStream = new FileOutputStream("E:\\ResourceInfo1.xls");
        //1.创建一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //2.创建一个工作表sheet
        HSSFSheet sheet = workbook.createSheet("ResourceInfo");
        //List<User> userList = userService.selectAll();
        //构造参数依次表示起始行，截至行，起始列， 截至列
        CellRangeAddress region=new CellRangeAddress(0, 0, 0, 31);
        sheet.addMergedRegion(region);

        HSSFCellStyle style=workbook.createCellStyle();
        //水平居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //垂直居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cell = row1.createCell(0);
        //设置值，这里合并单元格后相当于标题
        cell.setCellValue("资源维护数据");
        //将样式添加生效
        cell.setCellStyle(style);

        for(int i = 0;i<2;i++){
            //行
            HSSFRow row = sheet.createRow(i+1);
            //对列赋值
            row.createCell(0).setCellValue("ID");
            row.createCell(1).setCellValue("题名");
            row.createCell(2).setCellValue("其他题名");
            row.createCell(3).setCellValue("分辑名");
            row.createCell(4).setCellValue("印本ISSN");
            row.createCell(5).setCellValue("电子ISSN");
            row.createCell(6).setCellValue("印本isbn");
            row.createCell(7).setCellValue("电子isbn");
            row.createCell(8).setCellValue("订购号");
            row.createCell(9).setCellValue("种类");
            row.createCell(10).setCellValue("学科");
            row.createCell(11).setCellValue("出版社");
            row.createCell(12).setCellValue("国别");
            row.createCell(13).setCellValue("出版年");
            row.createCell(14).setCellValue("创刊年");
            row.createCell(15).setCellValue("语种");
            row.createCell(16).setCellValue("出版频率");
            row.createCell(17).setCellValue("分类号");
            row.createCell(18).setCellValue("期刊URL地址");
            row.createCell(19).setCellValue("载体");
            row.createCell(20).setCellValue("文献类型");
            row.createCell(21).setCellValue("出版模式");
            row.createCell(22).setCellValue("出版状态");
            row.createCell(23).setCellValue("价格");
            row.createCell(24).setCellValue("币种");
            row.createCell(25).setCellValue("文献来源");
            row.createCell(26).setCellValue("单位");


        }
        for(int i = 1;i<userList.size();i++){
            //行
            HSSFRow row = sheet.createRow(i+1);
            //对列赋值
            row.createCell(0).setCellValue(userList.get(i).getId());
            row.createCell(1).setCellValue(userList.get(i).getTitle());
            row.createCell(2).setCellValue(userList.get(i).getOtherTitle());
            row.createCell(3).setCellValue(userList.get(i).getSeriesTitle());
            row.createCell(4).setCellValue(userList.get(i).getPissn());
            row.createCell(5).setCellValue(userList.get(i).getEissn());
            row.createCell(6).setCellValue(userList.get(i).getPisbn());
            row.createCell(7).setCellValue(userList.get(i).getEisbn());
            row.createCell(8).setCellValue(userList.get(i).getOrderNumber());
            row.createCell(9).setCellValue(userList.get(i).getKindId());
            row.createCell(10).setCellValue(userList.get(i).getSubject());
            row.createCell(11).setCellValue(userList.get(i).getPublisher());
            row.createCell(12).setCellValue(userList.get(i).getCountry());
            row.createCell(13).setCellValue(userList.get(i).getPubYear());
            row.createCell(14).setCellValue(userList.get(i).getFoundedYear());
            row.createCell(15).setCellValue(userList.get(i).getLanguage());
            row.createCell(16).setCellValue(userList.get(i).getPubFeq());
            row.createCell(17).setCellValue(userList.get(i).getClassiNumber());
            row.createCell(18).setCellValue(userList.get(i).getUrl());
            row.createCell(19).setCellValue(userList.get(i).getCarrier());
            row.createCell(20).setCellValue(userList.get(i).getType());
            row.createCell(21).setCellValue(userList.get(i).getPubPattern());
            row.createCell(22).setCellValue(userList.get(i).getPubStatus());
            row.createCell(23).setCellValue(userList.get(i).getPrice());
            row.createCell(24).setCellValue(userList.get(i).getCurrency());
            row.createCell(25).setCellValue(userList.get(i).getLiterature());
            row.createCell(26).setCellValue(userList.get(i).getOrganId());

        }
        workbook.write(outputStream);
        outputStream.close();
    }

    //导入
    public static List<ResourceInfo> importExcel() throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File("E:\\ResourceInfo1.xls")));
        HSSFSheet sheet = null;

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            //获取每个sheet
            sheet = workbook.getSheetAt(i);
            List<ResourceInfo> list = new ArrayList<ResourceInfo>();
            //getPhysicalNumberOfRows获取有记录的行数
            for (int j = 2; j < sheet.getPhysicalNumberOfRows(); j++) {
                Row row = sheet.getRow(j);
                if (null != row) {
                    //getLastCellNum获取最后一列
                    ResourceInfo user = new ResourceInfo();
                    for (int k = 0; k < row.getLastCellNum(); k++) {
                        if (null != row.getCell(k)) {
                            if (k == 0) {
                                Cell cell = row.getCell(0);
                                //cell->string
                                user.setId(cell.getStringCellValue().toString());
                            }
                            if (k == 1) {
                                Cell cell = row.getCell(1);
                                //cell->string
                                user.setTitle(cell.getStringCellValue().toString());
                            }
                            if (k == 2) {
                                Cell cell = row.getCell(2);
                                user.setOtherTitle(cell.getStringCellValue().toString());
                            }
                            if (k == 3) {
                                Cell cell = row.getCell(3);
                                user.setSeriesTitle(cell.getStringCellValue().toString());
                            }
                            if (k == 4) {
                                Cell cell = row.getCell(4);
                                user.setPissn(cell.getStringCellValue().toString());
                            }
                            if (k == 5) {
                                Cell cell = row.getCell(5);
                                user.setEissn(cell.getStringCellValue().toString());
                            }
                            if (k == 6) {
                                Cell cell = row.getCell(6);
                                user.setPisbn(cell.getStringCellValue().toString());
                            }
                            if (k == 7) {
                                Cell cell = row.getCell(7);
                                user.setEisbn(cell.getStringCellValue().toString());
                            }
                            if (k == 8) {
                                Cell cell = row.getCell(8);
                                user.setOrderNumber(cell.getStringCellValue().toString());
                            }
                            if (k == 9) {
                                Cell cell = row.getCell(9);
                                user.setKindId(cell.getStringCellValue().toString());
                            }
                            if (k == 10) {
                                Cell cell = row.getCell(10);
                                user.setSubject(cell.getStringCellValue().toString());
                            }
                            if (k == 11) {
                                Cell cell = row.getCell(11);
                                user.setPublisher(cell.getStringCellValue().toString());
                            }
                            if (k == 12) {
                                Cell cell = row.getCell(12);
                                user.setCountry(cell.getStringCellValue().toString());
                            }
                            if (k == 13) {
                                Cell cell = row.getCell(13);
                                user.setPubYear(cell.getStringCellValue().toString());
                            }
                            if (k == 14) {
                                Cell cell = row.getCell(14);
                                user.setFoundedYear(cell.getStringCellValue().toString());
                            }
                            if (k == 15) {
                                Cell cell = row.getCell(15);
                                user.setLanguage(cell.getStringCellValue().toString());
                            }
                            if (k == 16) {
                                Cell cell = row.getCell(16);
                                user.setPubFeq(cell.getStringCellValue().toString());
                            }
                            if (k == 17) {
                                Cell cell = row.getCell(17);
                                user.setClassiNumber(cell.getStringCellValue().toString());
                            }
                            if (k == 18) {
                                Cell cell = row.getCell(18);
                                user.setUrl(cell.getStringCellValue().toString());
                            }
                            if (k == 19) {
                                Cell cell = row.getCell(19);
                                user.setCarrier(cell.getStringCellValue().toString());
                            }
                            if (k == 20) {
                                Cell cell = row.getCell(20);
                                user.setType(cell.getStringCellValue().toString());
                            }
                            if (k == 21) {
                                Cell cell = row.getCell(21);
                                user.setPubPattern(cell.getStringCellValue().toString());
                            }
                            if (k == 22) {
                                Cell cell = row.getCell(22);
                                user.setPubStatus(cell.getStringCellValue().toString());
                            }
                            if (k == 23) {
                                Cell cell = row.getCell(23);
                                user.setPrice(cell.getStringCellValue().toString());
                            }
                            if (k == 24) {
                                Cell cell = row.getCell(24);
                                user.setCurrency(cell.getStringCellValue().toString());
                            }if (k == 25) {
                                Cell cell = row.getCell(25);
                                user.setLiterature(cell.getStringCellValue().toString());
                            }if (k ==26) {
                                Cell cell = row.getCell(26);
                                user.setOrganId(cell.getStringCellValue().toString());
                            }
                        }
                    }
                    list.add(user);
                }
            }
            System.out.println("读取sheet表：" + workbook.getSheetName(i) + "完成");
            return list;
        }
        return null;
    }

}
